
import pandas as pd


d1 = pd.read_csv('Engine_CC.csv')
d2 = pd.read_csv('Female.csv')
d3 = pd.read_csv('Fuel.csv')
d4 = pd.read_csv('Male.csv')
d5 = pd.read_csv('Manufacturer.csv')
d6 = pd.read_csv('Model.csv')
d7 = pd.read_csv('Power.csv')
d8 = pd.read_csv('Price.csv')
d9 = pd.read_csv('Total.csv')
d10 = pd.read_csv('Transmission.csv')
d11 = pd.read_csv('Unknown.csv')


df = pd.merge(pd.merge(d1,d2,on='Index'),d3,on='Index')
df2 = pd.merge(pd.merge(df,d4,on='Index'),d5,on='Index')
df3 = pd.merge(pd.merge(df2,d6,on='Index'),d7,on='Index')
df4 = pd.merge(pd.merge(df3,d8,on='Index'),d9,on='Index')
merged_df = pd.merge(pd.merge(df4,d10,on='Index'),d11,on='Index')


merged_df.head() #Checking First 5 rows of Merged Dataframe


merged_df.drop("Index", axis=1, inplace=True) #Dropping index column in merged dataframe
merged_df.head()


missing_data = merged_df.isnull()
missing_data.head(5)


#Checking for missing values in each column
#False = No NULL/Missing value
#True = Value is Missing
for column in missing_data.columns.values.tolist():
    print(column)
    print (missing_data[column].value_counts())

Engine CC
False    6098
True        4
Name: Engine CC, dtype: int64
Female
False    6097
True        5
Name: Female, dtype: int64
Fuel
False    6097
True        5
Name: Fuel, dtype: int64
Male
False    6098
True        4
Name: Male, dtype: int64
Manufacturer
False    6101
True        1
Name: Manufacturer, dtype: int64
Model
False    6101
True        1
Name: Model, dtype: int64
Power
False    6097
True        5
Name: Power, dtype: int64
Price
False    6097
True        5
Name: Price, dtype: int64
Total
False    6097
True        5
Name: Total, dtype: int64
Transmission
False    6098
True        4
Name: Transmission, dtype: int64
Unknown
False    6097
True        5
Name: Unknown, dtype: int64


Manufacturer_list = ['Renault', 'BMW', 'Volkswagen', 'Peugeot', 'Ford', 'Nissan', 'Honda', 'Toyota', 'Mercedes', 'Audi', 'Citroen', 'Skoda', 'Land-Rover', 'Seat', 'Fiat', 'Mini', 'Saab', 'Hyundai', 'Kia', 'Jaguar', 'Mazda', 'Suzuki', 'Volvo', 'Rover', 'Mitsubishi', 'Smart', 'Porsche', 'Subaru', 'MG', 'Chrysler', 'Chevrolet', 'Alfa-Romeo', 'Daihatsu', 'Bentley', 'Daewoo', 'Dacia', 'Dodge', 'Lotus', 'Aston-Martin', 'Abarth', 'Ssangyong', 'Lexus', 'Maserati', 'Opel', 'Ferrari', 'TVR', 'Triumph', 'Lada', 'Daimler', 'Lancia', 'Datsun', 'Morris']
merged_df = merged_df[merged_df['Manufacturer'].isin(Manufacturer_list)]
merged_df.shape

(6097, 11)


Model_list = ['Clio', '320i', 'Polo', '206', 'Mondeo', 'Micra', 'Civic', 'Ka+', 'Megane', 'Yaris', 'CLA', 'A4', 'Passat', 'A3', '307', '207', 'Xsara', 'Fabia', '118i', 'Freelander', 'C3', 'Corolla', '535i', 'Avensis', 'E', 'Scenic', 'Ibiza', 'Range', 'Qashqai+2', 'Octavia', '500L', 'A', 'Almera', 'C4', 'C5', 'C6', 'Two', 'One', 'RAV4', 'RAV5', '9-3X', '107', 'Aygo', 'I10', 'I11', 'A6', 'Picanto', 'Note', 'Leon', 'XE', 'Fusion', 'Accord', 'MX-5', '6', 'C1', 'Swift', 'Galaxy', '308', '3', 'TT', 'Laguna', '2', 'Panda', 'Auris', '306', '106', 'V70', 'Saxo', 'Touran', 'Beetle', 'Rio', 'C-MAX', 'X5', 'Juke', 'Vitara', 'Ceed', '406', 'Getz', 'X-Trail', 'CLK', 'S40', '75', 'Berlingo', 'Alto', 'Primera', 'I20', 'SLK', 'S-MAX', 'ML', 'Sportage', '407', 'C2', 'I30', 'XF', 'A1', 'Colt', 'A5', 'S-Type', 'V50', 'Escort', 'XC90', 'V40', 'X3', 'Tiguan', 'B', 'Fortwo', 'Bora', '45', '911', 'XJ', 'Impreza', '09-May', 'Celica', 'Sharan', 'Santa', '208', 'S60', 'DS3', 'Modus', 'ZR', 'Voyager', 'Sorento', 'Z4', '2000', 'Up', 'Jimny', 'Matiz', 'Boxster', 'Wagon', '3008', '323', 'Puma', 'Stilo', 'Scirocco', 'XK', 'Altea', 'C30', 'Accent', 'Lupo', 'Sprinter', 'Ix35', 'Kangoo', '5', 'Jetta', 'Alhambra', 'Espace', 'Ignis', 'SX4', 'Carens', 'Q5', 'Partner', 'Yeti', 'Touareg', 'Seicento', 'Superb', 'Bravo', 'X1', 'Q7', '147', 'MR2', '740i', 'TF', 'S80', 'Fox', 'Twingo', 'Lancer', 'Doblo', 'Eos', 'M3', 'PT', 'Matrix', 'IQ', 'SLS', 'A2', 'Z3', 'C70', 'Roomster', 'Splash', 'Previa', 'Starlet', 'Aveo', 'XC70', 'Tucson', 'Legacy', 'Cayenne', '156', '640i', 'Spark', 'Kalos', 'Outlander', 'Pixo', 'FR-V', 'Sirion', 'Mini', 'Venga', 'Streetka', 'Felicia', 'Terrano', 'Space', 'Carisma', 'Lacetti', 'ZT', 'CLC', 'Defender', 'Arosa', 'HR-V', 'City-coupe', 'Terios', 'Carina', 'Multipla', 'A8', 'Citigo', 'Liana', 'Continental', 'Captiva', 'Pathfinder', '205', '900', 'B-MAX', '940', 'Toledo', 'Ix20', '850', 'Verso-S', 'S3', '159', 'Lanos', 'C8', '626', 'Sandero', '807', '5008', 'Cayman', '300C', '300', 'Duster', '1007', '508', 'Xantia', 'RCZ', '350Z', 'Demio', 'S2000', '190', 'Trajet', 'Tacuma', 'GT', 'Premacy', 'ZX', 'V60', 'Brava', 'S4', 'X6', 'Caliber', 'Maverick', 'Streetwise', 'CityRover', 'Cerato', 'Elise', 'Cougar', 'Roadster-coupe', 'Vantage', 'I40', 'Cruze', 'Exeo', 'Urban', 'Mii', 'A7', '405', '500C', 'Transporter', 'Spider', 'DS4', 'Picnic', 'Viano', 'Ulysse', 'Sierra', 'DB9', '80', 'Neon', 'Terracan', 'M550i', 'Magentis', 'AX', 'RS4', 'Captur', 'Grandis', 'Crossfire', 'CR-Z', 'R', 'Stream', 'Idea', 'Prelude', 'Elantra', '607', 'Rexton', 'CC', 'Roadster', '806', '9000', 'S5', 'Nemo', 'Sonata', 'Capri', 'Sedici', 'Brera', '944', 'Cordoba', 'C-Crosser', 'Cabriolet', 'Cinquecento', 'Galant', 'Qubo', 'Lantra', 'Baleno', '440', 'GS', '2008', 'S70', 'GT86', 'Caddy', '4007', 'GLK', 'Camry', 'Nitro', 'Sebring', 'Shuma', 'Veloster', 'Patrol', 'Expert', 'R8', 'Wind', 'Rapid', 'DB7', 'Corrado', 'Supra', 'Marea', 'Serena', 'Vaneo', 'Panamera', 'Copen', 'Logo', 'Paceman', '960', 'Tribute', 'M135i', 'Shuttle', 'Trafic', 'Journey', 'Phaeton', 'Justy', 'Orlando', 'Vito', 'Granada', 'Bipper', '370Z', 'RS6', 'John', 'Hilux', 'Legend', 'MX-3', 'GranCabrio', 'Scorpio', 'Rodius', 'Mustang', '121', 'Coupe', 'Integra', 'Carrera', 'Croma', 'Zafira', 'GT-R', 'DS5', 'Korando', '309', '100', 'Quattro', 'Probe', 'Focus', 'Fiesta', 'Golf', 'Discovery', 'XKR', 'Pride', '19', 'Concerto', 'Leganza', 'Bluebird', 'MPV', 'Xedos', 'Pajero', 'Orion', 'Vento', 'Nexia', 'Mentor', '460', '166', 'Maxima', 'Paseo', 'F430', '850i', 'Uno', 'Favorit', '146', 'MX-6', '145', 'Safrane', 'Move', '928', 'Musso', 'Cooper', 'Tipo', 'Espero', 'XM', 'CRX', 'Tuscan', 'Vel', '21', 'Prairie', 'SJ', 'Esprit', 'Samurai', '360', 'Tempra', '155', 'Barchetta', '90', 'TR7', 'Pony', '605', '25', '164', '11', '505', 'Marbella', 'Acclaim', 'X-90', 'Samara', '305', 'Griffith', '9', '33', 'Manta', 'Applause', '4', '3.6', 'Delta', 'Silvia', 'Sunny', '126', 'Tercel', 'Niva', 'Stellar', '99', '120', 'Kadett', '18', 'Dedra', 'Cherry', 'Marina', 'Laurel', 'Regata', '130', 'Thema', 'Stanza', 'Ital', 'Strada', 'Santana', 'Double', '105', 'Malaga', 'Ascona', 'Monza', 'Fuego', 'Derby', '104', 'Transit', 'Prisma', 'Senator', '127', '200', 'Rekord', '929', 'Cressida', 'Quintet', '14', '20', '1200', '504']
merged_df = merged_df[merged_df['Model'].isin(Model_list)]
merged_df.shape

(5950, 11)


merged_df.columns

Index(['Engine CC', 'Female', 'Fuel', 'Male', 'Manufacturer', 'Model', 'Power',
       'Price', 'Total', 'Transmission', 'Unknown'],
      dtype='object')


merged_df = merged_df[(merged_df.Price < 650.0) & (merged_df.Price > 0.0 ) & (merged_df.Transmission < 10.0) & (merged_df.Transmission > 0.0 ) & (merged_df.Power < 500.0) & (merged_df.Power > 0.0 ) & (merged_df['Engine CC'] < 6500.0) & (merged_df['Engine CC'] > 0.0 )]
merged_df.shape

(5933, 11)


missing_data = merged_df.isnull()
missing_data.head(5)


#Checking for missing values in each column
#False = No NULL/Missing value
for column in missing_data.columns.values.tolist():
    print(column)
    print (missing_data[column].value_counts())

Engine CC
False    5933
Name: Engine CC, dtype: int64
Female
False    5933
Name: Female, dtype: int64
Fuel
False    5933
Name: Fuel, dtype: int64
Male
False    5933
Name: Male, dtype: int64
Manufacturer
False    5933
Name: Manufacturer, dtype: int64
Model
False    5933
Name: Model, dtype: int64
Power
False    5933
Name: Power, dtype: int64
Price
False    5933
Name: Price, dtype: int64
Total
False    5933
Name: Total, dtype: int64
Transmission
False    5933
Name: Transmission, dtype: int64
Unknown
False    5933
Name: Unknown, dtype: int64


merged_df.dtypes

Engine CC       float64
Female           object
Fuel             object
Male             object
Manufacturer     object
Model            object
Power           float64
Price           float64
Total            object
Transmission    float64
Unknown          object
dtype: object


merged_df[["Transmission"]] = merged_df[["Transmission"]].astype("int")
merged_df[["Power"]] = merged_df[["Power"]].astype("int")
merged_df[["Engine_CC"]] = merged_df[["Engine CC"]].astype("int")


merged_df = merged_df.replace(',','', regex=True)
merged_df[["Male"]] = merged_df[["Male"]].astype(str).astype(int)
merged_df[["Female"]] = merged_df[["Female"]].astype(str).astype(int)
merged_df[["Unknown"]] = merged_df[["Unknown"]].astype(str).astype(int)
merged_df[["Total"]] = merged_df[["Total"]].astype(str).astype(int)


merged_df.dtypes

Engine CC       float64
Female            int32
Fuel             object
Male              int32
Manufacturer     object
Model            object
Power             int32
Price           float64
Total             int32
Transmission      int32
Unknown           int32
Engine_CC         int32
dtype: object


merged_df.head()


merged_df.to_csv('cleaned_car_buyers.csv', index=False)

	Index	Engine CC	Female	Fuel	Male	Manufacturer	Model	Power	Price	Total	Transmission	Unknown
0	0	1497.169492	422731	petrol	814172	Ford	Focus	-94.033898	30.619322	1293390	5.966102	56,487
1	1	1166.142857	631666	petrol	554879	Ford	Fiesta	68.571429	18.532143	1240602	5000.714286	54,057
2	2	1537.406593	310604	petrol	483216	Volkswagen	Golf	-89.461538	31.242154	841383	6.164835	47,563
3	3	1219.653846	312556	petrol	241287	Renault	Clio	75.576923	22.100000	581847	5.615385	28,004
4	4	1995.777778	115843	petrol	408016	BMW	320i	126.111111	47.848370	552984	6.444444	29,125

	Engine CC	Female	Fuel	Male	Manufacturer	Model	Power	Price	Total	Transmission	Unknown
0	1497.169492	422731	petrol	814172	Ford	Focus	-94.033898	30.619322	1293390	5.966102	56,487
1	1166.142857	631666	petrol	554879	Ford	Fiesta	68.571429	18.532143	1240602	5000.714286	54,057
2	1537.406593	310604	petrol	483216	Volkswagen	Golf	-89.461538	31.242154	841383	6.164835	47,563
3	1219.653846	312556	petrol	241287	Renault	Clio	75.576923	22.100000	581847	5.615385	28,004
4	1995.777778	115843	petrol	408016	BMW	320i	126.111111	47.848370	552984	6.444444	29,125

	Engine CC	Female	Fuel	Male	Manufacturer	Model	Power	Price	Total	Transmission	Unknown	Engine_CC
3	1219.653846	312556	petrol	241287	Renault	Clio	75	22.10000	581847	5	28004	1219
4	1995.777778	115843	petrol	408016	BMW	320i	126	47.84837	552984	6	29125	1995
5	1408.055556	299110	petrol	216333	Volkswagen	Polo	60	18.19250	547144	5	31701	1408
6	1631.500000	250614	petrol	178698	Peugeot	206	71	20.03375	455447	4	26135	1631
7	1998.500000	69603	petrol	357452	Ford	Mondeo	130	39.97375	443605	1	16550	1998

Step 1: Importing Required Library¶

Step 2: Reading All CSV Files into Pandas Dataframe¶

Step 3: Merging Dataframes into a Single Dataframe¶

Step 5: Check Missing Values¶

Step 4: Applying the following required conditions on the merged dataframe:¶

Step 5: Checking for Null/Missing Values Again¶

Step 6: Checking Datatypes¶

Step 6(A): Fixing Datatypes¶

Step 7: Convert Dataframe into CSV¶

	Engine CC	Female	Fuel	Male	Manufacturer	Model	Power	Price	Total	Transmission	Unknown
0	False	False	False	False	False	False	False	False	False	False	False
1	False	False	False	False	False	False	False	False	False	False	False
2	False	False	False	False	False	False	False	False	False	False	False
3	False	False	False	False	False	False	False	False	False	False	False
4	False	False	False	False	False	False	False	False	False	False	False